﻿using Bnbjoy.Domain.Abstract;
using Bnbjoy.Domain.Common;
using Bnbjoy.Domain.Entities;
using MySql.Data.MySqlClient;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Data.Entity.Validation;
using System.Dynamic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Bnbjoy.Domain.Concrete
{
    public class OrderRepository : IOrderRepository
    {
        public async Task<bool> InsertOrder(Order order, IEnumerable<Customer> customers, IEnumerable<OrderSpending> spendings) 
        {
            using (BnbjoyBackendDbEntities bnbjoyBackendDbEntities = new BnbjoyBackendDbEntities())
            {
                using (var transaction = bnbjoyBackendDbEntities.Database.BeginTransaction())
                {
                    try
                    {
                        bool insertOrder = false, insertCustomers = false, insertSpendings = false;
                        //判断该order id是否已经存在
                        var existedOrder = bnbjoyBackendDbEntities.Orders.AsNoTracking().Where(o=>o.OrderId == order.OrderId).FirstOrDefault();
                        if (existedOrder == null)
                        {
                            //插入新订单
                            bnbjoyBackendDbEntities.Orders.Add(order);
                            insertOrder = await bnbjoyBackendDbEntities.SaveChangesAsync() > 0;

                            //插入入住人信息
                            bnbjoyBackendDbEntities.Customers.AddRange(customers);
                            insertCustomers = await bnbjoyBackendDbEntities.SaveChangesAsync() > 0;

                            //插入消费条目信息
                            bnbjoyBackendDbEntities.OrderSpendings.AddRange(spendings);
                            insertSpendings = await bnbjoyBackendDbEntities.SaveChangesAsync() > 0;

                            if (insertOrder && insertCustomers && insertSpendings)
                            {
                                transaction.Commit();
                            }
                            //当房型，房间，默认价格都插入完成新建才算成功
                            return insertOrder && insertCustomers && insertSpendings;
                        }
                        else 
                        {
                            return false;
                        }
                    }
                    catch (DbEntityValidationException e)
                    {
                        foreach (var eve in e.EntityValidationErrors)
                        {
                            Console.WriteLine("Entity of type \"{0}\" in state \"{1}\" has the following validation errors:",
                                eve.Entry.Entity.GetType().Name, eve.Entry.State);
                            foreach (var ve in eve.ValidationErrors)
                            {
                                Console.WriteLine("- Property: \"{0}\", Error: \"{1}\"",
                                    ve.PropertyName, ve.ErrorMessage);
                            }
                        }
                        throw;
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        return false;
                    }
                }
            }
        }

        public async Task<dynamic> FindOrder(string orderId) 
        {
            using (BnbjoyBackendDbEntities bnbjoyBackendDbEntities = new BnbjoyBackendDbEntities())
            {
                try
                {
                    dynamic result = new ExpandoObject();
                    Order order = await bnbjoyBackendDbEntities.Orders.AsNoTracking().Where(o => o.OrderId == orderId).FirstOrDefaultAsync();
                    IEnumerable<Customer> customers = await bnbjoyBackendDbEntities.Customers.AsNoTracking().Where(c => c.OrderId == orderId).ToListAsync();
                    IEnumerable<OrderSpending> spendings = await bnbjoyBackendDbEntities.OrderSpendings.AsNoTracking().Where(s => s.OrderId == orderId).ToListAsync();

                    result.Order = order;
                    result.Customers = customers;
                    result.Spendings = spendings;

                    return result;
                }
                catch (Exception ex)
                {
                    return null;
                }
            }
        }

        public async Task<string> ObtainMobileArea(string mobileNumber) 
        {
            using (BnbjoyBackendDbEntities bnbjoyBackendDbEntities = new BnbjoyBackendDbEntities())
            {
                try
                {
                    var numberSection = mobileNumber.Substring(0, 7); //截取号码段
                    MobileAreaMap map = await bnbjoyBackendDbEntities.MobileAreaMaps.AsNoTracking().Where(m => m.MobileNumber == numberSection).FirstOrDefaultAsync();
                    var result = map.MobileArea.Replace(" ", "").Replace("市", "");
                    return result;
                }
                catch (Exception ex) 
                {
                    return "未知";
                }
            }
        }

        public async Task<int> TotalRoomCount(string bnbId) 
        {
            using (BnbjoyBackendDbEntities bnbjoyBackendDbEntities = new BnbjoyBackendDbEntities())
            {
                var roomCount = await bnbjoyBackendDbEntities.Rooms.Where(r => r.BnbId == bnbId).CountAsync();
                return roomCount;
            }
        }

        public async Task<IEnumerable<dynamic>> RetrieveAvailableRooms(string bnbId, DateTime fromDate)
        {
            try
            {
                var fromDateStr = fromDate.ToString("yyyy-MM-dd");
                using (BnbjoyBackendDbEntities bnbjoyBackendDbEntities = new BnbjoyBackendDbEntities())
                {
                    string sql = "select d.DAY Date, Count(o.OrderId) RoomCount from `Order` o right join " +
                            "(" +
                            "SELECT ADDDATE(@param1, INTERVAL @i:=@i+1 DAY) AS DAY " +
                            "FROM (" +
                            "SELECT a.a " +
                            "FROM (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a " +
                            "CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b " +
                            "CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c " +
                            ") a " +
                            "JOIN (SELECT @i := -1) r1 " +
                            "WHERE " +
                            "@i < 29 " +
                            ") d on d.DAY >= o.FromDate and d.DAY < o.ToDate and o.OrderStatus != 4 and o.BnbId = @param2 group by d.DAY";

                    dynamic results = bnbjoyBackendDbEntities.Database.DynamicSqlQuery(sql, new MySqlParameter("param1", fromDateStr), new MySqlParameter("param2", bnbId));
                    string resultJson = JsonConvert.SerializeObject(results);
                    IEnumerable<dynamic> list = JsonConvert.DeserializeObject<List<dynamic>>(resultJson);
                    return list;
                }
            }
            catch (Exception ex) 
            {
                return null;
            }
        }
    }
}
